/**
 * 20080124 1156: Eric Kao was created.
 */

package pre;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import com.e885.log.E885Logger;

/**
 * 測試用,例如:要使用 TEST_TABLE, 新要產生一個 class, 並 implemnt 這個 AbstractPreTable class<br>
 * <br>
 * Copyright: Copyright (c) 2001-2008<br>
 * Company: E885 Group
 * 
 * @author Eric.Kao(gau168.gau@msa.hinet.net)
 * @version 1.0
 */
public abstract class AbstractPreTable
{
	/**
	 * DB connection
	 */
	private Connection connection;

	/**
	 * statement
	 */
	private PreparedStatement statement;

	/**
	 * 已知 DB Connnection<br>
	 * <br>
	 * 
	 * @param connection db connection
	 */
	public AbstractPreTable( Connection connection )
	{
		this.connection = connection;
	}

	/**
	 * 已知 PreparedStatement<br>
	 * <br>
	 * 
	 * @param statement the instance of the PreparedStatement
	 * @throws SQLException
	 */
	public AbstractPreTable( PreparedStatement statement ) throws SQLException
	{
		this.statement = statement;

		connection = statement.getConnection();
	}

	/**
	 * get connection<br>
	 * <br>
	 * 
	 * @return
	 */
	public Connection getConnection()
	{
		return connection;
	}

	/**
	 * get statement<br>
	 * <br>
	 * 
	 * @return
	 */
	public PreparedStatement getPreparedStatement()
	{
		return statement;
	}

	/**
	 * @param statement the statement to set
	 */
	public void setStatement( PreparedStatement statement )
	{
		this.statement = statement;
	}

	/**
	 * 建立 table, 依照 <code>generateCreatingTableSQL()</code>來決定建立的 table<br>
	 * <br>
	 * 
	 * @throws SQLException
	 */
	public void createTable() throws SQLException
	{
		String tempString = generateCreatingTableSQL();

		E885Logger.testLog.debug( tempString );

		execute( tempString );

		E885Logger.testLog.info( "It has already created table." );

		tempString = null;
	}

	/**
	 * 刪除 table, 依照 <code>generateDroppingTableSQL</code>來決定刪除的 table<br>
	 * <br>
	 * 
	 * @throws SQLException
	 */
	public void dropTable() throws SQLException
	{
		String tempString = generateDroppingTableSQL();

		E885Logger.testLog.debug( tempString );

		execute( tempString );

		E885Logger.testLog.info( "It has already dropped table." );

		tempString = null;
	}

	/**
	 * 查詢 table 的資料, 依照 <code>generateSelectingDataSQL</code> 來決定查詢 table 的資料<br>
	 * <br>
	 * 
	 * @return ResultSet 查詢的結果
	 * @throws SQLException
	 */
	public ResultSet selectData() throws SQLException
	{
		String tempString = generateSelectingDataSQL();

		E885Logger.testLog.debug( tempString );

		return generateStatement( tempString ).executeQuery();
	}

	/**
	 * 新增一筆資料, 依照 <code>generateInsertingDataSQLs</code> 來決定新增 table 的資料<br>
	 * <br>
	 * 
	 * @throws SQLException
	 */
	public void insertData() throws SQLException
	{
		insertData( 1 );
	}

	/**
	 * 新增資料, 數量由 <code>size</code> 來決定, 並依照 <code>generatneInsertingDataSQLs</code>來決定新增 table 的資料<br>
	 * <br>
	 * 
	 * @param size 新增的數量
	 * @throws SQLException
	 */
	public void insertData( int size ) throws SQLException
	{
		if( size == 0 )
		{
			return;
		}

		Statement statement = connection.createStatement();

		String[] sqls = generateInsertingDataSQLs( size );

		int length = sqls.length;

		for( int i = 0; i < length; i++ )
		{
			if( i < 50 )
			{
				E885Logger.testLog.debug( sqls[ i ] );
			}

			if( i == 50 )
			{
				E885Logger.testLog.debug( "Too Many Message.Syste is running for inserting data." );
			}

			statement.addBatch( sqls[ i ] );
		}

		E885Logger.testLog.info( "executing batch......" );

		statement.executeBatch();

		E885Logger.testLog.info( "It has already inserted data into table.Total count:" + size );
	}

	/**
	 * 修改資料, 依 <code>generateUpdatingDataSQL</code> 來決定修改的資料<br>
	 * <br>
	 * 
	 * @param id 修改資料的 primary key
	 * @throws SQLException
	 */
	public void updateData( String id ) throws SQLException
	{
		String tempString = generateUpdatingDataSQL( id );

		E885Logger.testLog.debug( tempString );

		execute( tempString );

		E885Logger.testLog.info( "It has already updated data into table." );

		tempString = null;
	}

	/**
	 * 刪除資料, 依 <code>generateDeletingDataSQL</code> 來決定刪除的資料<br>
	 * <br>
	 * 
	 * @param id 刪除資料的 primary key
	 * @throws SQLException
	 */
	public void deleteData( String id ) throws SQLException
	{
		String tempString = generateDeletingDataSQL( id );

		E885Logger.testLog.debug( tempString );

		execute( tempString );

		E885Logger.testLog.info( "It has already deleted data into table." );

		tempString = null;
	}

	/**
	 * 假如 statement 不是從 constructor 取得, 那麼就會產生一個新的 PreparedStatement
	 * 
	 * @param sql sql statement
	 * @return the class of the PreparedStatement
	 * @throws SQLException
	 */
	public PreparedStatement generateStatement( String sql ) throws SQLException
	{
		if( statement != null )
		{
			return statement;
		}

		return connection.prepareStatement( sql, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE );
	}

	private void execute( String sql ) throws SQLException
	{
		generateStatement( sql ).execute();
	}

	/**
	 * generate sql statement for creating table
	 * 
	 * @return
	 */
	protected abstract String generateCreatingTableSQL();

	/**
	 * generate sql statement for dropping table
	 * 
	 * @return
	 */
	protected abstract String generateDroppingTableSQL();

	/**
	 * generate sql statement for selecting
	 * 
	 * @return
	 */
	protected abstract String generateSelectingDataSQL();

	/**
	 * generate sql statement for inserting
	 * 
	 * @param size
	 * @return
	 */
	protected abstract String[] generateInsertingDataSQLs( int size );

	/**
	 * generate sql statement for updating
	 * 
	 * @param id
	 * @return
	 */
	protected abstract String generateUpdatingDataSQL( String id );

	/**
	 * generate sql statement for deleting
	 * 
	 * @param id
	 * @return
	 */
	protected abstract String generateDeletingDataSQL( String id );
}
